clear all
set more off, perm
local mydir "`1'"
global Tables	`mydir'/XS/Tables
global Work	`mydir'/XS/DataWork
global Raw	`mydir'/XS/DataRaw
global WRDS	`mydir'/WRDS
/* ********************************************************************* */
* Get CRSP data to adjust for splits
import sas $WRDS/CRSP/dsf.sas7bdat, clear
rename *, lower
replace prc=abs(prc)
format date %td
keep permco permno date cfacshr cfacpr shrout prc
/* **************************** */
save $Work/crsp, replace
/* **************************** */
import sas using $WRDS/CRSP/stocknames.sas7bdat, clear case(lower)
rename *, lower

keep if (shrcd==10 | shrcd==11) & (exchcd>=1 & exchcd<=3)

keep permco permno namedt nameenddt siccd
duplicates drop

* find end-of-the-month day for the last record of each stock
sort permco permno nameenddt
bys permco permno:  gen obs=_n
bys permco permno:  gen tot=_N
replace nameenddt=mdy(12,31,year(nameenddt)) 			if month(nameenddt)==12 & obs==tot
replace nameenddt=mdy(month(nameenddt)+1,1,year(nameenddt))-1	if month(nameenddt)<=11 & obs==tot
drop obs tot

joinby permco permno using $Work/crsp
keep if date>=namedt & date<=nameenddt 

* a few observations (1.8%) have overlapping namedt-nameenddt ranges 
drop namedt nameenddt
duplicates drop

* remaining duplicates are coming from siccd
duplicates drop permco permno date, force 
bys permco date: gen nsecurities=_N
/* **************************** */
save $Work/crsp, replace
/* ********************************************************************* */
* Get IBES data 
import sas using $WRDS/IBES/actpsumu_epsus.sas7bdat, case(lower) clear
keep if index(curcode,"USD")>0 
keep ticker statpers shout price fy0a 
tempfile shout
save "`shout'", replace
* *********************
import sas using $WRDS/IBES/March2021/statsumu_epsus.sas7bdat, case(lower) clear
keep if (index(measure,"EPS")>0  & index(curcode,"USD")>0) 
drop if index(fpi,"A") | index(fpi,"B") | index(fpi,"C") | index(fpi,"D")

* tickers that include a "/" store estimates for non-primary issues 
* affects 21,057 obs out of 12,989,213
drop if index(ticker,"/")

destring fpi, replace
keep cname ticker oftic cusip statpers fpi fpedats medest
* *********************
* Keep LTG data separate from "medest" (medest is interpolated; LTG is not)
preserve
keep if fpi==0
rename medest LTG
drop fpi fpedats
tempfile ltg

save "`ltg'", replace
* *********************
restore
* interpolate medest

keep if fpi<=5 & fpi>0

gen fdate=mofd(fpedats)
format fdate %tm

egen i=group(ticker statpers) 
tsset i fdate
tsfill

replace statpers=L1.statpers if missing(statpers)
foreach var of varlist ticker cn* cusip oftic {
	by i: replace `var'=`var'[_n-1] if missing(`var')==1 
}

by i: ipolate medest fdate, gen(imedest) 
gen myfpi=fdate-mofd(statpers)
bys i: egen max=max(myfpi)

keep if (myfpi/12)>=1 | (myfpi==max & max==11)
keep if (myfpi/12)==int(myfpi/12) | (myfpi==max & max==11)

replace fpi=myfpi/12
replace fpi=1 if (myfpi==max & max==11)
replace medest=imedest

keep ticker oftic cname cusip statpers fpi fdate medest 
* ****************************************
* i reshape to make sure that i have a 1:1 merge with link file
reshape wide medest fdate, i(ticker oftic cname cusip statpers) j(fpi)
merge 1:1 ticker statpers using "`ltg'", update

* observations with LTG but no medest will have fdate==.
replace  fdate1=mofd(statpers)+12 if _merge==2
drop _merge

merge 1:1 ticker statpers using "`shout'"
drop if _merge==2
drop _merge

*drop data for 2023
drop if year(statpers)>2022

merge 1:1 ticker statpers using $Raw/link_ibes_crsp2022, keepusing(permco permno ticker statpers comnam cname) 
keep if _merge==3
drop _merge

* split-adjust using daily crsp data (and limit sample to common stock)
gen date=statpers
format date %td
merge 1:1 permco permno date using $Work/crsp
keep if _merge==3
drop _merge date

replace  fdate1=fdate2-12 if fdate1==.
replace  fdate2=fdate1+12 if fdate2==.
replace  fdate3=fdate1+24 if fdate3==.
replace  fdate4=fdate1+36 if fdate4==.
replace  fdate5=fdate1+48 if fdate5==.

forvalues x=1(1)5 {
	gen est`x'=medest`x'
}
replace est4=est3*(1+LTG/100)	if est3>0 & est4==.
replace est5=est4*(1+LTG/100)	if est4>0 & est5==.

order *, sequential
forvalues x=1(1)5 {
	generate MEDEST`x'=est`x'*shrout
	replace  medest`x'=medest`x'/cfacshr
}
drop LTG cfacpr prc est1 est2 est3 est4 est5 
reshape long medest MEDEST fdate, i(ticker oftic cname cusip statpers cfacshr shrout shout price fy0a) j(fpi)
/* ********************************************************************* */
* get data on financials @ the end of forecast period
gen datem=fdate
format datem %tm
merge m:1 permco permno datem using $Work/qtly, keepusing(permco permno datem ibqLTM niqLTM streetLTM operLTM bookeqLTM divLTM saleqLTM shout_compust)  
drop if _merge==2
drop _merge datem oftic cusip

reshape wide medest MEDEST ibqLTM niqLTM streetLTM operLTM bookeqLTM divLTM saleqLTM shout_compust fdate, i(ticker cname permco permno nsecurities statpers) j(fpi)
order *, sequential
* **************
* add lagged earnings data
generate datem=mofd(statpers)
format datem %tm
merge m:1 permco permno datem using $Work/qtly, keepusing(permco permno datem *LTM shout_compust)

order ticker cname statpers shout_compust shout shrout 

egen id=group(permco permno)
tsset id datem
forvalues x=1(1)5 {
	local j=`x'*12
	quietly: gen L`x'saleqLTM =L`j'.saleqLTM
	quietly: gen L`x'bookeqLTM=L`j'.bookeqLTM
	quietly: gen L`x'ibqLTM   =L`j'.ibqLTM
	quietly: gen L`x'niqLTM   =L`j'.niqLTM
	quietly: gen L`x'operLTM  =L`j'.operLTM
	quietly: gen L`x'streetLTM=L`j'.streetLTM
	}
drop if _merge==2
drop _merge
* add LTG 
merge 1:1 permco permno datem using $Work/XSibes, keepusing(permco permno ticker statpers LTG) 
*merge 1:1 permco permno statpers using $Work/ibes, keepusing(permco permno ticker statpers LTG) 
keep if _merge==3
drop _merge

format fdate* %tm
order *LTM*, sequential
/* ********************************************************************* */
order permco permno ticker cname nsecurities statpers siccd datem fdate* LTG medest* MEDEST1-MEDEST5 saleqLTM1-saleqLTM5 ibqLTM ibqLTM1-ibqLTM5 niqLTM niqLTM1-niqLTM5 streetLTM streetLTM1-streetLTM5 operLTM1-operLTM5

save $Work/FamaFrench05b1, replace
/* ********************************************************************* */
* deal with dilution
import sas $WRDS/CRSP/msf.sas7bdat, clear case(lower)
gen shs=cfacshr*shrout
gen datem=mofd(date)
format datem %tm

keep permco permno datem shs

egen id=group(permco permno)
tsset id datem

forvalues x=1(1)5 {
	local t=12*`x'
	gen Lshs`x'=L`t'.shs
	gen shs`x' =F`t'.shs 
}
rename shs shs0
drop id
merge 1:1 permco permno datem using $Work/FamaFrench05b1
drop if _merge==1
drop _merge
save $Work/FamaFrench05b2, replace 
/* ********************************************************************* */
* add bookeq, etc
use permco permno datem date_size mcap_size bookeq bm op inv SIZE BOOK PROF INVE rSIZE using $Work/FamaFrench04, clear
tsset permco datem
tsfill
replace date_size=L.date_size if date_size==.

foreach var of varlist permno mcap_size bookeq bm op inv SIZE BOOK PROF INVE rSIZE {
	replace `var'=L.`var' if `var'==. & datem-date_size<12
}
merge 1:1 permco permno datem using $Work/FamaFrench05b2
drop if _merge==1
drop _merge
order permco permno ticker cname statpers datem siccd fdate* LTG medest* MEDEST1-MEDEST5 mcap_size bookeq bm op inv SIZE BOOK PROF INVE rSIZE saleqLTM1-saleqLTM5 ibqLTM ibqLTM1-ibqLTM5 niqLTM niqLTM1-niqLTM5 streetLTM streetLTM1-streetLTM5 operLTM1-operLTM5
drop id	
save $Work/FamaFrench05bb, replace 
/* ********************************************************************* */
!rm $Work/FamaFrench05b1.dta -f
!rm $Work/FamaFrench05b2.dta -f
!rm $Work/ibes.dta -f
!rm $Work/crsp.dta -f
/* ********************************************************************* */
